A database engine (engine) is a computer program for storing and retrieving data. Such data is often limited to text (letters, numerals, symbols, and other characters), but may include any data that may be stored by a computer. Most engines store data in tables. A table is a series of rows, also called records. Each row contains data about a particular thing, such as an employee. The rows are divided into columns. The intersection of a row and column is referred to as a field. Each column specifies a particular type of data that is contained in each field of the column. Each field contains the data of the particular type for the intersecting row and column. Example columns for an Employees table include Name, Title, Manager, and CompensationType. A row of such a table would contain the name, title, manager, and compensation type for a particular employee, each in a separate field. Any column (or group of columns) may be designated as a unique index column. The engine ensures that unique index columns remain unique by not permitting any rows to be added to a table where the new row's unique index field duplicates the unique index field of an existing row.
FIG. 1A is a table diagram showing the contents of a sample table named Employees. The Employees table 100 contains 27 rows 101-127. Each row contains a field for each of four columns: a Name column 131, a Title column 132, a Manager column 133, and a CompensationType column 134. For instance, row 101 has a Name field that contains "Andrea", a Title field that contains "Tester", a Manager field that contains "Rose", and a CompensationType field that contains "salary". The Name column is a unique index column of the Employee's Table.
An engine may be used directly by a person or by a database front-end program. Such persons and database front-end programs will be referred to hereafter collectively as users of the engine. An engine typically receives instructions to retrieve data stored in tables, called queries, from a user. Queries are usually expressed in one of several common query languages, such as Structured Query Language (SQL).
A query specifies the data to be retrieved and the manipulations to be made to the retrieved data. A query therefore specifies the table or tables from which data is to be retrieved (the source tables), selection criteria for the rows to be retrieved, the definition of derived fields, and other related information. A derived field is a field that contains data that is derived from one or more other fields.
In response to a query, a conventional (typical) engine performs data retrieval and manipulation specified by the query. In doing so, the conventional engine generates a conventional query table to hold the query result. The conventional query table contains a "static" view of the query result. A user may access query result by invoking primitive operations (primitives) defined by the conventional engine. The conventional engine may provide primitives to browse through the query result, that is, step through the rows of the conventional query table and retrieve data that the conventional engine stored in the fields while processing the query. By invoking these primitives, the user can retrieve the results of the query.
As an example of a query, the user could construct and submit a query to select the salaried employees from the Employees table, then use a Managers table to determine each employee's department number based on the name of the employee's manager. FIG. 1B is a table diagram showing the contents of the Managers table. The Managers table 150 contains 9 rows 151-159. Each row contains a field for each of two columns: a Manager column 161 and a DepartmentNumber column 162. The Manager column is a unique index column of the Manager's table. If expressed in SQL, the query would appear as follows:
1 SELECT Employees.Name, Employees.Manager, PA1 2 Managers.DepartmentNumber PA1 3 FROM Employees, Managers Employees INNER JOIN Managers PA1 4 ON Employees.Manager=Managers.Manager PA1 5 WHERE Employees.CompensationType="salary";